﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataAccess.Exceptions;
using Model;

namespace DataAccess
{
    public static class InscriptionDataAccess
    {
        public static string ConnString = Conexion.DataSource;

        public static Inscription ModifyInscription(Inscription studentInscription)
        {
            if (studentInscription != null)
            {
                int finalScore = (studentInscription.score1 + studentInscription.score2 + studentInscription.score3) / 3;
                SqlConnection connection = null;
                connection = new SqlConnection(ConnString);
                try
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "UPDATE Inscripcion1 SET codMateria=@codMateria, gestion=@gestion, nota1=@nota1, nota2=@nota2, nota3=@nota3, notaFinal=@notaFinal  WHERE codInscripcion=@codInscripcion ";
                    command.Parameters.AddWithValue("codMateria", studentInscription.codM);
                    command.Parameters.AddWithValue("gestion", studentInscription.Gestion);
                    command.Parameters.AddWithValue("codInscripcion", studentInscription.codI);
                    command.Parameters.AddWithValue("nota1", studentInscription.score1);
                    command.Parameters.AddWithValue("nota2", studentInscription.score2);
                    command.Parameters.AddWithValue("nota3", studentInscription.score3);
                    command.Parameters.AddWithValue("notaFinal", finalScore);

                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error modifying an inscription");
                    }
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error modifying an inscription", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return studentInscription;
        }

        public static Inscription NewInscription(Inscription studentInscription)
        {
            if (studentInscription != null)
            {
                SqlConnection co = null;
                try
                {
                    co = new SqlConnection(ConnString);
                    SqlCommand cmd = co.CreateCommand();
                    cmd.CommandText = "INSERT INTO Inscripcion1 (ciEstudiante,codMateria,gestion,nota1,nota2,nota3,notaFinal)" +
                                      " VALUES (@ciEstudiante,@codMateria,@gestion,@nota1,@nota2,@nota3,@notaFinal)";
                    cmd.Parameters.AddWithValue("ciEstudiante", studentInscription.ciEstudiante);
                    cmd.Parameters.AddWithValue("codMateria", studentInscription.codM);
                    cmd.Parameters.AddWithValue("gestion", studentInscription.Gestion);
                    cmd.Parameters.AddWithValue("nota1", 0);
                    cmd.Parameters.AddWithValue("nota2", 0);
                    cmd.Parameters.AddWithValue("nota3", 0);
                    cmd.Parameters.AddWithValue("notaFinal", 0);


                    SqlCommand getID = co.CreateCommand();
                    getID.CommandText = "SELECT @@Identity";

                    co.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting an Inscription");
                    }
                    studentInscription.codI = Convert.ToInt64(getID.ExecuteScalar(), CultureInfo.CurrentCulture);

                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error inserting an Inscription", ex);
                }
                finally
                {
                    if (co != null)
                    {
                        co.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return studentInscription;
        }

        public static Collection<Inscription> GetInscriptionByStudentID(int id)
        {
            SqlConnection co = null;
            Collection<Inscription> studentsList = null;
            try
            {
                studentsList = new Collection<Inscription>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                //cmd.CommandText = "SELECT * FROM Inscripcion i, Materia m WHERE ciEstudiante LIKE @V_ciEstudiante and i.codMateria=m.codMateria ";

                cmd.CommandText = "SELECT DISTINCT * FROM Inscripcion1 i, Materia m, Estudiante e WHERE i.ciEstudiante=e.ci and i.codMateria=m.codMateria and ciEstudiante = ciEstudiante";
                cmd.Parameters.AddWithValue("ciEstudiante", id);
                //cmd.Parameters.AddWithValue("V_ciEstudiante", id + "%");
                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                Collection<Student> studenResultByid = StudentDataAccess.GetStudentsByID(id);
                int i = 0;
                while (reader.Read())
                {

                    Inscription studentResult = new Inscription();
                    Subject subjectResult = new Subject();

                    studentResult.ciEstudiante = Convert.ToInt32(reader["ciEstudiante"].ToString());
                    studentResult.codM = Convert.ToInt64(reader["codMateria"].ToString());
                    studentResult.Gestion = reader["gestion"].ToString();
                    studentResult.codI = Convert.ToInt64(reader["codInscripcion"].ToString());
                    subjectResult.nameSubject = reader["nombre"].ToString();
                    studentResult.Apellido = studenResultByid[i].lastname;
                    studentResult.Nombre = studenResultByid[i].name;
                    studentResult.Materia = subjectResult.nameSubject;
                    studentsList.Add(studentResult);
                    ++i;
                }
                return (studentsList);
                /*int i = 0;
                while (reader.Read())
                {
                    Inscription studentResult = new Inscription();
                    Subject subjectResult = new Subject();
                    studentResult.ciEstudiante = Convert.ToInt32(reader["ciEstudiante"].ToString());
                    studentResult.codM = Convert.ToInt64(reader["codMateria"].ToString());
                    studentResult.Gestion = reader["gestion"].ToString();
                    studentResult.codI = Convert.ToInt64(reader["codInscripcion"].ToString());
                    subjectResult.nameSubject = reader["nombre"].ToString();
                    studentResult.Apellido = reader["apePat"].ToString();
                    studentResult.Nombre = reader["e.nombre"].ToString();
                    studentResult.Materia = subjectResult.nameSubject;
                    studentsList.Add(studentResult);
                    ++i;
                }
                return (studentsList);*/
            }

            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting student inscription", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Inscription> GetInscriptionByStudentName(string name)
        {
            SqlConnection co = null;
            Collection<Inscription> studentsList = null;
            try
            {
                studentsList = new Collection<Inscription>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM Inscripcion1 i, Materia m, Estudiante e WHERE e.nombre=@nombre and i.ciEstudiante=e.ci and i.codMateria=m.codMateria";

                cmd.Parameters.AddWithValue("nombre", name);

                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                Collection<Student> studenResultByname = StudentDataAccess.GetStudentsByName(name);
                int i = 0;
                while (reader.Read())
                {
                    Inscription studentResult = new Inscription();
                    Subject subjectResult = new Subject();
                    studentResult.ciEstudiante = Convert.ToInt32(reader["ciEstudiante"].ToString());
                    studentResult.codM = Convert.ToInt64(reader["codMateria"].ToString());
                    studentResult.Gestion = reader["gestion"].ToString();
                    studentResult.codI = Convert.ToInt64(reader["codInscripcion"].ToString());
                    subjectResult.nameSubject = reader["nombre"].ToString();
                    studentResult.Apellido = studenResultByname[i].lastname;
                    studentResult.Nombre = studenResultByname[i].name;
                    studentResult.Materia = subjectResult.nameSubject;
                    studentsList.Add(studentResult);
                    ++i;
                }
                return (studentsList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting student inscription", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Inscription> GetInscriptionBySubjectname(string subjectName)
        {
            SqlConnection co = null;
            Collection<Inscription> studentsList = null;
            try
            {
                studentsList = new Collection<Inscription>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM Inscripcion1 i, Materia m, Estudiante e WHERE m.nombre LIKE @V_nombre and i.ciEstudiante=e.ci and i.codMateria=m.codMateria";

                cmd.Parameters.AddWithValue("V_nombre", subjectName + "%");

                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                Student studenResultByname = new Student();
                int i = 0;
                while (reader.Read())
                {
                    Inscription studentResult = new Inscription();
                    Subject subjectResult = new Subject();
                    studenResultByname = StudentDataAccess.GetOneStudentByID(Convert.ToInt32(reader["ciEstudiante"].ToString()));

                    studentResult.ciEstudiante = Convert.ToInt32(reader["ciEstudiante"].ToString());
                    studentResult.codM = Convert.ToInt64(reader["codMateria"].ToString());
                    studentResult.Gestion = reader["gestion"].ToString();
                    studentResult.codI = Convert.ToInt64(reader["codInscripcion"].ToString());
                    subjectResult.nameSubject = reader["nombre"].ToString();
                    studentResult.score1 = Convert.ToInt16(reader["nota1"].ToString());
                    studentResult.score2 = Convert.ToInt16(reader["nota2"].ToString());
                    studentResult.score3 = Convert.ToInt16(reader["nota3"].ToString());
                    studentResult.finalScore = Convert.ToInt16(reader["notaFinal"].ToString());

                    studentResult.Apellido = studenResultByname.lastname;
                    studentResult.Nombre = studenResultByname.name;
                    studentResult.Materia = subjectResult.nameSubject;
                    studentsList.Add(studentResult);
                    ++i;
                }
                return (studentsList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting student inscription", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Inscription> GetInscriptionBySubjectSigla(string subjectSigla)
        {
            SqlConnection co = null;
            Collection<Inscription> studentsList = null;
            try
            {
                studentsList = new Collection<Inscription>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM Inscripcion1 i, Materia m, Estudiante e WHERE sigla LIKE @V_sigla and i.ciEstudiante=e.ci and i.codMateria=m.codMateria";

                cmd.Parameters.AddWithValue("V_sigla", subjectSigla + "%");

                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                Student studenResultByname = new Student();
                int i = 0;
                while (reader.Read())
                {
                    Inscription studentResult = new Inscription();
                    Subject subjectResult = new Subject();
                    studenResultByname = StudentDataAccess.GetOneStudentByID(Convert.ToInt32(reader["ciEstudiante"].ToString()));

                    studentResult.ciEstudiante = Convert.ToInt32(reader["ciEstudiante"].ToString());
                    studentResult.codM = Convert.ToInt64(reader["codMateria"].ToString());
                    studentResult.Gestion = reader["gestion"].ToString();
                    studentResult.codI = Convert.ToInt64(reader["codInscripcion"].ToString());
                    subjectResult.nameSubject = reader["nombre"].ToString();
                    studentResult.score1 = Convert.ToInt16(reader["nota1"].ToString());
                    studentResult.score2 = Convert.ToInt16(reader["nota2"].ToString());
                    studentResult.score3 = Convert.ToInt16(reader["nota3"].ToString());
                    studentResult.finalScore = Convert.ToInt16(reader["notaFinal"].ToString());

                    studentResult.Apellido = studenResultByname.lastname;
                    studentResult.Nombre = studenResultByname.name;
                    studentResult.Materia = subjectResult.nameSubject;
                    studentsList.Add(studentResult);
                    ++i;
                }
                return (studentsList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting student inscription", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Inscription> GetInscriptionBySubjectCode(long subjectCode)
        {
            SqlConnection co = null;
            Collection<Inscription> studentsList = null;
            try
            {
                studentsList = new Collection<Inscription>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM Inscripcion1 i, Materia m, Estudiante e WHERE m.codMateria LIKE @V_codMateria and i.ciEstudiante=e.ci and i.codMateria=m.codMateria";

                cmd.Parameters.AddWithValue("V_codMateria", subjectCode + "%");

                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                Student studenResultByname = new Student();
                int i = 0;
                while (reader.Read())
                {
                    Inscription studentResult = new Inscription();
                    Subject subjectResult = new Subject();
                    studenResultByname = StudentDataAccess.GetOneStudentByID(Convert.ToInt32(reader["ciEstudiante"].ToString()));

                    studentResult.ciEstudiante = Convert.ToInt32(reader["ciEstudiante"].ToString());
                    studentResult.codM = Convert.ToInt64(reader["codMateria"].ToString());
                    studentResult.Gestion = reader["gestion"].ToString();
                    studentResult.codI = Convert.ToInt64(reader["codInscripcion"].ToString());
                    studentResult.score1 = Convert.ToInt16(reader["nota1"].ToString());
                    studentResult.score2 = Convert.ToInt16(reader["nota2"].ToString());
                    studentResult.score3 = Convert.ToInt16(reader["nota3"].ToString());
                    studentResult.finalScore = Convert.ToInt16(reader["notaFinal"].ToString());
                    subjectResult.nameSubject = reader["nombre"].ToString();
                    studentResult.Apellido = studenResultByname.lastname;
                    studentResult.Nombre = studenResultByname.name;
                    studentResult.Materia = subjectResult.nameSubject;
                    studentsList.Add(studentResult);
                    ++i;
                }
                return (studentsList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting student inscription", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Student> GetAllInscriptions()
        {
            SqlConnection co = null;
            Collection<Student> studentsList = null;
            try
            {
                studentsList = new Collection<Student>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM Estudiante, Materia";
                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Student studentResult = new Student();
                    studentResult.observations = reader["observaciones"].ToString();

                    studentsList.Add(studentResult);
                }
                return (studentsList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting student inscriptions", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        //WEB
        public static Collection<Inscription> GetInscriptionByStudentid(Student student)
        {
            if (student != null)
            {
                SqlConnection co = null;
                Collection<Inscription> studentsList = null;
                try
                {
                    studentsList = new Collection<Inscription>();
                    co = new SqlConnection(ConnString);
                    SqlCommand cmd = co.CreateCommand();
                    cmd.CommandText = "SELECT i.codMateria,nombre,gestion FROM Inscripcion1 i,Materia m WHERE ciEstudiante=@ciEstudiante and i.codMateria=m.codMateria";

                    cmd.Parameters.AddWithValue("ciEstudiante", student.id);

                    co.Open();
                    SqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        Inscription inscription = new Inscription();
                        inscription.ciEstudiante = student.id;
                        inscription.Gestion = reader["gestion"].ToString();
                        inscription.Materia = reader["nombre"].ToString();
                        inscription.codI = Convert.ToInt64(reader["codMateria"].ToString(), CultureInfo.CurrentCulture);
                        inscription.Apellido = student.lastname;
                        inscription.Nombre = student.name;
                        studentsList.Add(inscription);
                    }
                    return (studentsList);
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error getting student inscription", ex);
                }
                finally
                {
                    if (co != null)
                    {
                        co.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return null;
        }
        public static Collection<long> GetMySubjects(int studentId)
        {
            SqlConnection co = null;
            Collection<long> subjectsList = null;
            try
            {
                subjectsList = new Collection<long>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT codMateria FROM INSCRIPCION1 WHERE ciEstudiante=@ciEstudiante";

                cmd.Parameters.AddWithValue("ciEstudiante", studentId);

                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    subjectsList.Add(Convert.ToInt64(reader["codMateria"].ToString(), CultureInfo.CurrentCulture));
                }
                return (subjectsList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting student subjects from table inscription", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static object GetSubjectScores(long codSubject, int ciEstudiante)
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = null;
            SqlDataAdapter adapter = null;
            try
            {
                ds = new DataSet();
                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT nota1,nota2,nota3 FROM INSCRIPCION1  where codMateria=@codMateria and ciEstudiante=@ciEstudiante ";
                command.Parameters.AddWithValue("codMateria", codSubject);
                command.Parameters.AddWithValue("ciEstudiante", ciEstudiante);
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "INSCRIPCION1");
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["INSCRIPCION1"].DefaultView;
                return dataView;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting subject scores", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }


        public static object GetKardex(int idEstudiante)
        {
            SqlConnection connection = null;
            DataView dataView = null;
            DataSet ds = null;
            SqlDataAdapter adapter = null;
            try
            {
                ds = new DataSet();
                ds.Locale = CultureInfo.CurrentCulture;
                connection = new SqlConnection(ConnString);
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT gestion,codMateria,nota1,nota2,nota3,notaFinal FROM INSCRIPCION1  where ciEstudiante=@ciEstudiante and notaFinal>50 GROUP BY gestion,codMateria,nota1,nota2,nota3,notaFinal ";
                command.Parameters.AddWithValue("ciEstudiante", idEstudiante);
                connection.Open();
                adapter = new SqlDataAdapter(command);
                adapter.Fill(ds, "INSCRIPCION1");
                if (ds.HasErrors) ds.RejectChanges(); else ds.AcceptChanges();
                dataView = ds.Tables["INSCRIPCION1"].DefaultView;
                return dataView;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting kardex", ex);
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
                if (ds != null)
                {
                    ds.Dispose();
                }
                if (adapter != null)
                {
                    adapter.Dispose();
                }
                SqlConnection.ClearAllPools();
            }
        }
    }
}
